package com.zl_huang.example.dao;

import java.util.ArrayList;
import java.util.List;

import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.type.IntegerType;
import org.hibernate.type.StringType;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;
import com.zl_huang.example.model.Subject;
@Repository
public class SubjectDaoHibernateImpl extends BaseHibernateDAO<Subject> implements ISubjectDaoHibernate {
	@Autowired
	private SessionFactory sessionFactory;
	@Transactional
	public Session getSession(){
		return this.sessionFactory.getCurrentSession();
	}

	@Override
	@Transactional
	public Integer createSubject(Subject subject) throws Exception {
		Session session = getSession();
		session.save(subject);
		return 1;
	}

	@Override
	@Transactional
	public Integer editSubject(Subject subject) throws Exception {
		Session session = getSession();
		session.update(subject);
		return 1;
	}

	@Override
	@Transactional
	public Integer deleteSubject(int sub_id) throws Exception {
		Session session = getSession();
		Subject subject = (Subject) session.get(Subject.class, sub_id);
		if(subject != null){
			session.delete(subject);
			return 1;
		}else{
			return 0;
		}
	}

	@Override
	@Transactional
	public List<Subject> findAllSubject() throws Exception {
		Session session = getSession();
		String hql = "from Subject";
		@SuppressWarnings("unchecked")
		List<Subject> subjectList = session.createQuery(hql).list();
		return subjectList;
	}

	@Override
	@Transactional
	public Subject getSubjectById(int sub_id) throws Exception {
		Session session = getSession();
		return (Subject) session.get(Subject.class, sub_id);
	}

	@SuppressWarnings("unchecked")
	@Override
	@Transactional
	public List<String> getAllUserBySubId(Integer sub_id) throws Exception {
		StringBuilder sql = new StringBuilder();
		sql.append(" select user.user_id as userId ");
		sql.append(" from exam_user_subject eus ");
		sql.append(" left join exam_subject es on eus.sub_id = es.sub_id ");
		sql.append(" left join exam_user user on user.user_id = eus.user_id ");
		sql.append(" where es.sub_id = :sub_id ");		
		return this.createSqlQuery(String.valueOf(sql))
				.addScalar("userId", StringType.INSTANCE)
				.setInteger("sub_id", sub_id).list();
	}

	@Override
	@Transactional
	public void insertSubjectUsers(List<String> insertList, Integer sub_id) throws Exception {
	Session session = getSession();
		if(!insertList.isEmpty()){
			for(String user_id : insertList){
				String sql = "insert into exam_user_subject(user_id,sub_id) values(?,?)";
				SQLQuery query =  session.createSQLQuery(sql);
				query.setString(0, user_id);
				query.setInteger(1, sub_id);
				System.out.println("新勾选上的学生数："+query.executeUpdate());
				
			}
		}		
	}

	@Override
	@Transactional
	public void deleteSubjectUsers(List<String> delList, Integer sub_id) throws Exception {
		Session session = getSession();
		if(!delList.isEmpty()){
			for(String user_id : delList){
				String sql = "delete from exam_user_subject where user_id = ? and sub_id = ?";
				SQLQuery query =  session.createSQLQuery(sql);
				query.setString(0, user_id);
				query.setInteger(1, sub_id);
				System.out.println("放弃勾选的学生数有："+query.executeUpdate());
				
			}
		}		
		
	}

	@SuppressWarnings("unchecked")
	@Override
	@Transactional
	public List<Subject> getAllSubjectByUserId(String user_id) throws Exception {
		List<Subject> subjectList = new ArrayList<Subject>();
		StringBuilder sql = new StringBuilder();
		sql.append(" select es.sub_id as sub_id");
		sql.append(" from exam_user_subject eus ");
		sql.append(" left join exam_subject es on eus.sub_id = es.sub_id ");
		sql.append(" left join exam_user user on user.user_id = eus.user_id ");
		sql.append(" where eus.user_id = :user_id ");		
		List<Integer> list = this.createSqlQuery(String.valueOf(sql))
				             .addScalar("sub_id", IntegerType.INSTANCE)
				             .setString("user_id", user_id).list();	
		for(Integer sub_id : list){
			System.out.println(sub_id);
			subjectList.add(getSubjectById(sub_id));
		}
		return subjectList;
	}

}
